CREATE TABLE [dbo].[Audit](
	[AuditId] [int] IDENTITY(1,1) NOT NULL,
	[AuditType] [nvarchar](500) NOT NULL,
	[AuditSummary] [text] NULL,
 CONSTRAINT [PK_Audit] PRIMARY KEY CLUSTERED 
(
	[AuditId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE TABLE [dbo].[RecordItem](
	[RecordId] [int] IDENTITY(1,1) NOT NULL,
	[RecordNumber] [nvarchar](50) NOT NULL,
	[RackNumber] [nvarchar](50) NOT NULL,
	[TrayNumber] [nvarchar](50) NOT NULL,
	[Title] [nvarchar](1000) NULL,
	[Author] [nvarchar](500) NULL,
	[Description] [text] NULL,
	[DescriptionSinhala] [text] NULL,
	[DescriptionTamil] [text] NULL,
	[CreatedDate] [datetime] NOT NULL,
	[CreatedBy] [nvarchar](50) NOT NULL,
	[LastUpdatedBy] [nvarchar](50) NULL,
	[LastUpdatedDate] [datetime] NULL,
	[IsDeleted] [bit] NOT NULL,
 CONSTRAINT [PK_RecordItem] PRIMARY KEY CLUSTERED 
(
	[RecordId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE TABLE [dbo].[RecordItemImages](
	[ImageId] [int] IDENTITY(1,1) NOT NULL,
	[RecordId] [int] NOT NULL,
	[Image] [varbinary](max) NOT NULL,
	[ImageBinary] [image] NULL,
 CONSTRAINT [PK_RecordItemImages] PRIMARY KEY CLUSTERED 
(
	[ImageId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

CREATE TABLE [dbo].[Roles](
	[RoleId] [int] IDENTITY(1,1) NOT NULL,
	[RoleName] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_Roles] PRIMARY KEY CLUSTERED 
(
	[RoleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[User](
	[UserId] [int] IDENTITY(1,1) NOT NULL,
	[UserName] [nvarchar](50) NOT NULL,
	[Password] [nvarchar](200) NOT NULL,
	[FirstName] [nvarchar](200) NULL,
	[LastName] [nvarchar](200) NULL,
	[IsDeleted] [bit] NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
	[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE TABLE [dbo].[UserRoles](
	[UserRoleId] [int] IDENTITY(1,1) NOT NULL,
	[UserId] [int] NOT NULL,
	[RoleId] [int] NOT NULL,
 CONSTRAINT [PK_UserRoles] PRIMARY KEY CLUSTERED 
(
	[UserRoleId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO


CREATE TABLE [dbo].[SearchHistory](
	[SearchId] [int] IDENTITY(1,1) NOT NULL,
	[RecordId] [int] NOT NULL,
	[SearchDate] [datetime] NOT NULL,
	[SearchBy] [nvarchar](100) NULL,
 CONSTRAINT [PK_SearchHistory] PRIMARY KEY CLUSTERED 
(
	[SearchId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[SearchHistory] ADD  CONSTRAINT [DF_SearchHistory_SearchDate]  DEFAULT (getdate()) FOR [SearchDate]
GO

INSERT INTO [dbo].[Roles]
           ([RoleName])
     VALUES ('Administrator')
INSERT INTO [dbo].[Roles]
           ([RoleName])
     VALUES ('Operator')
	 INSERT INTO [dbo].[Roles]
           ([RoleName])
     VALUES ('User')

INSERT INTO [dbo].[User]
    ([UserName],[Password],[FirstName],[LastName],[IsDeleted])
     VALUES ('super','FHffQ2IvfMTEs0j3uXvn0TJxVOntaPci+NuwUmKZxvg=','Super','Administrator',0)

INSERT INTO [dbo].[User]
    ([UserName],[Password],[FirstName],[LastName],[IsDeleted])
     VALUES ('admin','HTz+3rc+H9pWnltCww0iCPNV95tKHnJW3I0u3xcn/io=','System','Administrator',0)



INSERT INTO [dbo].[UserRoles]
           ([UserId]
           ,[RoleId])
     VALUES (1,1)
INSERT INTO [dbo].[UserRoles]
           ([UserId]
           ,[RoleId])
     VALUES (1,2)
INSERT INTO [dbo].[UserRoles]
           ([UserId]
           ,[RoleId])
     VALUES (2,1)
INSERT INTO [dbo].[UserRoles]
           ([UserId]
           ,[RoleId])
     VALUES (2,2)